﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBUnitility
{
    /// <summary>
    /// 通用数据分页类
    /// </summary>
    public class SqlDataPager
    {
        #region 一般属性

        /// <summary>
        /// 每页显示的条数
        /// </summary>
        public int PageSize { get; set; }
        /// <summary>
        /// 需要显示的字段（以逗号分隔 ）
        /// </summary>
        public string FiledName { get; set; }
        /// <summary>
        /// 外部连接
        /// </summary>
        public string InnerName { get; set; }
        /// <summary>
        /// 表的名称
        /// </summary>
        public string TableName { get; set; }
        /// <summary>
        /// 查询条件
        /// </summary>
        public string Condition { get; set; }
        /// <summary>
        /// 表的主键或唯一键
        /// </summary>
        public string PrimaryKey { get; set; }
        /// <summary>
        /// 当前页码
        /// </summary>
        public int CurrentPage { get; set; }
        /// <summary>
        /// 排序条件
        /// </summary>
        public string Sort { get; set; }

        #endregion

        #region 只读属性

        /// <summary>
        /// 记录的总数【不能直接赋值】
        /// </summary>
        //  public int RecordCount { get; set; }//此中手设计不安全
        private int recordCount;
        public int RecordCount//设置只读属性，外面不能直接赋值
        {
            get { return recordCount; }
        }
        /// <summary>
        /// 总页数
        /// </summary>
        public int TotalPages
        {
            get
            {
                if (recordCount != 0)//如果查询记录总数不为0
                {
                    if (recordCount % PageSize != 0)
                    {
                        return recordCount / PageSize + 1;
                    }
                    else
                    {
                        return recordCount / PageSize;
                    }
                }
                else
                {
                    this.CurrentPage = 1;//如果查询没有数据，则当前页码需要复位
                    return 0;
                }
            }
        }
        #endregion

        //分页查询方法所用的SQL语句
        private string GetPagedSQL()
        {
            //计算需要过滤的总数
            string filterCount = (PageSize * (CurrentPage - 1)).ToString();
            //组合SQL语句
            string sql = "select  Top {0} {1} from {2} {3} where {4}  and {5} not in  ";
            sql += "(select Top {6}  {7} from {8} where {9} order by {10} ) order by {11};";
            sql += "select count(*) from {12} where {13}";
            sql = string.Format(sql, PageSize, FiledName, TableName, InnerName,Condition,
                PrimaryKey, filterCount, PrimaryKey, TableName, Condition, Sort, Sort, TableName, Condition);
            return sql;
        }
        /// <summary>
        /// 执行分页查询，返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable GetPagedData()
        {
            //【1】执行查询，返回分页后的结果集
            DataSet ds = SQLHelper.GetDataSet(GetPagedSQL());
            //【2】获取满足记录的总数
            this.recordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
    }
}
